package com.zn.product.dao;

import com.zn.DBUtil;
import com.zn.Util;
import com.zn.product.model.Product;

import java.io.File;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ProductDao {
    public List<Product> getProductList(){
        List<Product> list = null;
        Connection conn = DBUtil.getConnection();
        String sql = "select * from tb_product order by create_time desc limit 10";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while(rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setDescription(rs.getString("description"));
                product.setMarket_price(rs.getDouble("market_price"));
                product.setSell_price(rs.getDouble("sell_price"));
                product.setPicture(rs.getString("picture"));
                list.add(product);
                product = null;
            }
        }catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
            DBUtil.closeResource(conn,ps,rs);
        }
        return list;
    }
    public Product getProductById(String id){
        Connection conn = DBUtil.getConnection();
        String sql = "select * from tb_product where id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps =conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            if(rs.next()) {
                Product product = new Product();
                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setDescription(rs.getString("description"));
                product.setMarket_price(rs.getDouble("market_price"));
                product.setSell_price(rs.getDouble("sell_price"));
                product.setPicture(rs.getString("picture"));
                return product;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn,ps,rs);
        }
        return null;
    }
    public int addCar(String customerId,String productId,String number){
        Connection conn = DBUtil.getConnection();
        String sql="INSERT INTO `tb_car`(`customerid`,`productid`,`number`) VALUES(?,?,?)";
        PreparedStatement ps = null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            ps.setString(3, number);
            int result=ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn,ps,null);
        }
    }
    public int updateCar(String customerId,String productId,String number){
        Connection conn = DBUtil.getConnection();
        String sql="Update `tb_car`set `number`=? where `customerid`=?and `productid`=?";
        PreparedStatement ps = null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            ps.setString(3, number);
            int result=ps.executeUpdate();
            return result;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn,ps,null);
        }
    }
    public int isExist(String customerId,String productId){
        Connection conn = DBUtil.getConnection();
        String sql="selact * from tb_car where `customerid`=? and `productid`=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setString(1, customerId);
            ps.setString(2, productId);
            rs = ps.executeQuery();
            if(rs.next()) {
                int number = rs.getInt("number");
                return number;
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn,ps,rs);
        }
        return 0;
    }
    public List<ShoppingCar>getShoppingCarInfo(String customerId){
        Connection conn = DBUtil.getConnection();
        String sql="select * from tb_car join tb_product on tb_car.product.id=tb_product.id where tb_car.customerid=?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<ShoppingCar> list = null;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1, customerId);
            rs = ps.executeQuery();
            while(rs.next()) {
                ShoppingCar shoppingCar=new ShoppingCar();
                shoppingCar.setCustomerId(rs.getInt("customerid"));
                shoppingCar.setProductId(rs.getInt("productid"));
                shoppingCar.setDescription(rs.getString("description"));
                shoppingCar.setMarket_price(rs.getDouble("market_price"));
                shoppingCar.setSell_price(rs.getDouble("sell_price"));
                shoppingCar.setPicture(rs.getString("picture"));
                shoppingCar.setName(rs.getString("name"));
                shoppingCar.setNumber(rs.getInt("number"));
                list.add(shoppingCar);
                shoppingCar = null;
            }
            return list;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(conn,ps,rs);
        }

    }
    public int addOrder(String phone,String total_price , List<ShoppingCar> list){
        Connection conn = DBUtil.getConnection();
        String sql="INSERT INTO `tb_order`(`order_id`,`mobile`,`total_price`)VALUES(?,?,?)";
        PreparedStatement ps = null;
        String orderId= Util.createOrderId();
        int n=0;
        try {
            ps=conn.prepareStatement(sql);
            ps.setString(1, orderId);
            ps.setString(2, phone);
            ps.setString(3, total_price);
            n=ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            DBUtil.closeResource(null,ps,null);
        }
        if(n>0) {
            Connection conn2=DBUtil.getConnection();
            String sql2="INSERT INTO `tb_order_item`(`order_id`,`product_id`,`product_name`,`product_price`,`amount`)VALUES(?,?,?,?,?))";
            PreparedStatement ps2=null;
            try {
                ps2=conn2.prepareStatement(sql2);
                for (int i=0;i<list.size();i++) {
                    ShoppingCar shoppingCar=list.get(i);
                    ps2.setString(1, orderId);
                    ps2.setInt(2, shoppingCar.getProductId());
                    ps2.setString(3, shoppingCar.getName());
                    ps2.setDouble(4,shoppingCar.getSell_price());
                    ps2.setInt(5, shoppingCar.getNumber());
                    ps2.addBatch();
                }
                ps2.executeBatch();
                return 1;
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }finally {
                DBUtil.closeResource(conn,ps,null);
            }
        }
        return 0;
    }
}
